"""
1、打开文件
2、读取标题
3、读取所有的数据
4、指定单元格写入数据（使用静态方法，不要使用实例方法）
"""

from openpyxl import load_workbook
# import openpyx1
from common.tools import get_path


class ExcelHandler():
    # 操作Excel
    def __init__(self, file):
        # 初始化函数
        self.file = file

    def open_sheet(self, name):
        # 打开表单
        wb = load_workbook(self.file)
        sheet = wb[name]
        return sheet

    def header(self, sheet_name):
        # 获取表单的表头
        sheet = self.open_sheet(sheet_name)
        headers = []
        for i in sheet[1]:
            headers.append(i.value)
        return headers

    def read(self, sheet_name):
        # 读取所有的数据
        sheet = self.open_sheet(sheet_name)
        rows = list(sheet.rows)
        data = []
        for row in rows[1:]:
            row_data = []
            for cell in row:
                row_data.append(cell.value)
                # 列表转成字典：要和header去zip
                data_dict = dict(zip(self.header(sheet_name), row_data))
            data.append(data_dict)
        return data

    @staticmethod
    def write(file, sheet_name, row, column, data):
        # 写人EXceL 数据
        wb = load_workbook(file)
        sheet = wb[sheet_name]
        # 修改单元格
        sheet.cell(row, column).value = data
        # 保存
        wb.save(file)
        # 关闭
        wb.close()

if __name__=='__main__':
    excel=ExcelHandler(get_path("/data/test_excel.xlsx"))
    sheet=excel.open_sheet('sheet1')
    header=excel.header("sheet1")
    print(header)
    data=excel.read("sheet1")
    print(data)
    excel.write(get_path("/data/test_excel.xlsx"),"sheet1",3,1,"Bob2")
